# -*- coding:utf-8 -*-
"""
book表实现增删改查
"""
import logging
import random

import sqlite3

from LibrarySystem.config.config import Config
from LibrarySystem.repositories.sqlite3.interface.book_data_interface import BookDataInterface
from LibrarySystem.repositories.sqlite3.models.book_models import Book

module_logger = logging.getLogger("logger")


class BookRepository(BookDataInterface):
    """
    @ClassName：BookRepository
    @Description： 继承图书数据层接口类
    负责实现接口功能
    @Author：锦沐Python
    """
    def __init__(self):
        self.config = Config()
        self.connection = None
        self.cursor = None
        # 建立数据库连接
        self.connection = sqlite3.connect(self.config.SQLite_DB_PATH, check_same_thread=False, timeout=20)
        self.connection.row_factory = self._dict_factory

    def _dict_factory(self, cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d

    def _execute_mysql(self, sql_command, data=None):
        try:
            # self.connection.ping(reconnect=True)
            self.cursor = self.connection.cursor()
            self.cursor.execute(sql_command, data)

            if sql_command.lower().strip().startswith("select"):
                # 如果是查询语句，获取查询结果
                result = self.cursor.fetchall()
                return (True, result)
            else:
                # 对于增删改操作，提交事务
                self.connection.commit()
                affected_rows = self.cursor.rowcount
                return (True, f"操作成功，影响行数: {affected_rows}")

        except Exception as e:
            # 发生异常时回滚事务
            self.connection.rollback()
            module_logger.error(f"数据库操作失败: {e}")
            return (False, f"数据库操作失败: {e}")


    def add_book(self, book: Book):
        """
        添加书籍
        :param book:
        :return:
        """
        book_id = book.book_id
        try:
            sql = "SELECT * FROM books WHERE book_id = ? LIMIT 1"
            data = (book_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag and result:
                module_logger.info("books 已存在")
                return (False, f"books {book_id} 已存在")

            # 写入数据
            sql = "INSERT INTO books (book_id, book_name, ISBN, record_time," \
                  "category, author, publisher, publication_date," \
                  "borrowed_by, borrow_time, loan_period," \
                  "due_time, img_url)" \
                  " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

            data = (book.book_id, book.book_name, book.ISBN, book.record_time,
                    book.category, book.author, book.publisher, book.publication_date,
                    book.borrowed_by, book.borrow_time, book.loan_period, book.due_time,
                    book.img_url)

            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag:
                return (True, f"books {book_id} 添加成功")
            else:
                module_logger.info(f"添加书籍失败")
                return (False, f"添加书籍失败")

        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"添加书籍失败：{e}")
            return (False, f"添加书籍失败：{e}")

    def update_book(self, book: Book):
        """
        根据book_id更新书籍信息
        :param book:
        :return:
        """

        book_id = book.book_id

        try:
            # 读数据
            sql = "SELECT * FROM books WHERE book_id = ? LIMIT 1"
            data = (book_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag is False:
                module_logger.info(f"书籍{book_id}不存在，更新失败")
                return (False, f"书籍{book_id}不存在，更新失败")

            # 写入数据
            sql = "UPDATE books SET book_name=?, ISBN=?, record_time=?, " \
                  "category=?, author=?, publisher=?, publication_date=?, " \
                  "borrowed_by=?, borrow_time=?, loan_period=?, " \
                  "due_time=?, img_url=? WHERE book_id=?"

            data = (book.book_name, book.ISBN, book.record_time,
                    book.category, book.author, book.publisher, book.publication_date,
                    book.borrowed_by, book.borrow_time, book.loan_period,
                    book.due_time, book.img_url, book.book_id)

            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag and result:
                module_logger.info(f"书籍{book_id} 更新成功")
                return (True, f"书籍{book_id} 更新成功")
            else:
                module_logger.error(f"更新失败 {book_id}")
                return (False, f"更新失败 {book_id}")

        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"修改书籍信息失败：{e}")
            return (False, f"修改书籍信息失败：{e}")

    def query_book(self, book_id: str):
        """
            查询书籍
            :param book_id:
            :return:
        """
        try:
            # 读数据
            sql = "SELECT * FROM books WHERE book_id = ? LIMIT 1"
            data = (book_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag is False:
                module_logger.info(f"书籍{book_id}不存在，查询失败")
                return (False, f"书籍{book_id}不存在，查询失败")

            # 创建一个 Book 对象 解决异常：tuple index out of range
            if result:
                get_book = Book(**result[0])
                return (True, get_book)
            else:
                module_logger.info(f"书籍{book_id}不存在，查询失败")
                return (False, f"书籍{book_id}不存在，查询失败")
        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"查询书籍信息失败：{e}")
            return (False, f"查询书籍信息失败：{e}")

    def del_book(self, book_id: str):
        """
        删除书籍
        :param book_id:
        :return:
        """
        try:
            # 读数据
            sql = "SELECT * FROM books WHERE book_id = ? LIMIT 1"
            data = (book_id,)
            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag is False:
                module_logger.info(f"书籍{book_id}不存在，删除失败")
                return (False, f"书籍{book_id}不存在，删除失败")

            # 写入数据
            sql = "DELETE FROM books WHERE book_id = ?"
            data = (book_id)

            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag:
                return (True, f"书籍{book_id} 删除成功")
            else:
                return (False, f"删除书籍信息失败")

        except Exception as e:
            # 处理其他可能的异常
            module_logger.info(f"删除书籍信息失败：{e}")
            return (False, f"删除书籍信息失败：{e}")

    def get_books(self, count: int):
        """
        :param count:  小于 0 表示获取全部
        :return:
        """
        book_list = []
        try:
            # 读数据
            sql = "SELECT * FROM books ORDER BY RANDOM() LIMIT ?"
            data = (count,)
            if count <= 0:
                sql = "SELECT * FROM books"
                # 注意此处使用空元组可以避开： parameters are of unsupported type
                data = ()

            # 遍历查找
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)

            if flag is False:
                module_logger.info(f"获取书籍失败")
                return (False, f"获取书籍失败")

            for book_data in result:
                book_list.append(Book(**book_data))

            # 顺序打乱，制造随机性展示
            random.shuffle(book_list)

            if count > 0:
                book_list = book_list[:count]
            return (True, book_list)

        except Exception as e:
            module_logger.info(f"获取书籍失败: {e}")
            return (False, f"获取书籍失败：{e}")

    def get_books_by_keywords(self, fields_to_check:list, keywords:str):

        # 构造 SQL 查询语句
        sql = "SELECT * FROM books WHERE "
        conditions = []
        data = []
        book_list = []
        # 构造 WHERE 子句，使用 OR 连接多个字段的模糊查询条件
        for field in fields_to_check:
            conditions.append(f"{field} LIKE ?")
            data.append(f"%{keywords}%")

        try:
            # 使用 OR 连接多个字段的条件
            sql += " OR ".join(conditions)

            (flag, result) = self._execute_mysql(sql_command=sql, data=tuple(data))
            if flag:
                for book_data in result:
                    book_list.append(Book(**book_data))

            # 顺序打乱，制造随机性展示
            random.shuffle(book_list)
            return (True, book_list)

        except Exception as e:
            module_logger.error(f"根据关键词获取书籍错误：{e}")
            return (False, f"根据关键词获取书籍错误：{e}")

    def get_books_by_column(self, column: str, value:str):
        # 构造 SQL 查询语句
        sql = f"SELECT * FROM books WHERE {column} = ?"
        data = (value,)
        book_list = []

        try:
            (flag, result) = self._execute_mysql(sql_command=sql, data=data)
            if flag:
                for book_data in result:
                    book_list.append(Book(**book_data))

                # 顺序打乱，制造随机性展示
                random.shuffle(book_list)
                return (True, book_list)

        except Exception as e:
            module_logger.error(f"根据列名获取书籍错误：{e}")
            return (False, f"根据列名获取书籍错误：{e}")

